package sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import beans.ProjectItemBean;
import beans.ProjectsBean;

public class ProjectsDao {
	private DataAccess dataAccess = new DataAccess();

	public ProjectsBean getProjects() throws ClassNotFoundException, SQLException {
		ProjectsBean projectsBean = null;
		
		String query = "SELECT ID, NAME, DESCRIPTION FROM PROJECTS ORDER BY NAME";
	
		ResultSet rset = dataAccess.getSelectResult(query);
		
		projectsBean = new ProjectsBean();
		while (rset.next()) {
			ProjectItemBean projectItemBean = new ProjectItemBean();
			projectItemBean.setId(rset.getInt("ID"));
			projectItemBean.setName(rset.getString("NAME"));
			projectItemBean.setDescription(rset.getString("DESCRIPTION"));
				
			projectsBean.addRow(projectItemBean);
		}
		dataAccess.closeConnection();
		return projectsBean;
	}

	public ProjectItemBean getProjectData(int projectId) throws ClassNotFoundException, SQLException {
		ProjectItemBean projectItemBean = new ProjectItemBean();

		String query = "SELECT ID, NAME, DESCRIPTION FROM PROJECTS WHERE ID = " + projectId;
		ResultSet rset = dataAccess.getSelectResult(query);
		while (rset.next()) {
			projectItemBean.setId(rset.getInt("ID"));
			projectItemBean.setName(rset.getString("NAME"));
			projectItemBean.setDescription(rset.getString("DESCRIPTION"));
		}
		dataAccess.closeConnection();
		
		return projectItemBean;
	}

	public ProjectItemBean getProjectData(String projectName) throws ClassNotFoundException, SQLException {
		ProjectItemBean projectItemBean = new ProjectItemBean();
		String query = "SELECT ID, NAME, DESCRIPTION FROM PROJECTS WHERE BINARY NAME = ?";

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setString(1, projectName);
		
		ResultSet rset = dataAccess.getSelectResult(preparedStatement);
		while (rset.next()) {
			projectItemBean.setId(rset.getInt("ID"));
			projectItemBean.setName(rset.getString("NAME"));
			projectItemBean.setDescription(rset.getString("DESCRIPTION"));
			projectName = rset.getString("NAME");
		}
		dataAccess.closeConnection();
		
		return projectItemBean;
	}
	
	public int insert(ProjectItemBean projectItem) throws ClassNotFoundException, SQLException {
		String query = "INSERT INTO PROJECTS (NAME, DESCRIPTION) VALUES (?, ?)";
				
		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setString(1, projectItem.getName());
		preparedStatement.setString(2, projectItem.getDescription());

		int rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		if (rowsUpdated == 1) {
			// get id of new data
			ResultSet rset = dataAccess.getSelectResult("SELECT LAST_INSERT_ID()");
			while (rset.next()) {
				projectItem.setId(rset.getInt(1));
			}
			rset.close();
		}
		dataAccess.closeConnection();

		return rowsUpdated;
	}
	
	public int update(ProjectItemBean projectItemBean) throws ClassNotFoundException, SQLException {
		String query = "UPDATE PROJECTS SET NAME=?, DESCRIPTION=? WHERE ID=?";

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);

		preparedStatement.setString(1, projectItemBean.getName());
		preparedStatement.setString(2, projectItemBean.getDescription());
		preparedStatement.setInt(3, projectItemBean.getId());
					
		int rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		dataAccess.closeConnection();

		return rowsUpdated;
	}
	
	public int delete(int projectId) throws ClassNotFoundException, SQLException {
		String deleteProjectQuery = "DELETE FROM PROJECTS WHERE ID = ?"; 
		String updateTeamMembersQuery2 = "UPDATE TEAM_MEMBERS SET PROJECT_ID = NULL WHERE PROJECT_ID = ?";
		Connection conn = null;
		PreparedStatement deleteProjectPreparedStatement = null;
		PreparedStatement updateTeamMembersPreparedStatement = null;
		int deleteProjectRowsUpdated = 0;
		int updateTeamMembersRowsUpdated = 0;

		try {
			conn = dataAccess.getConnection();
			conn.setAutoCommit(false);
	
			// unassign team members first to remove foreign key constraint
			updateTeamMembersPreparedStatement = conn.prepareStatement(updateTeamMembersQuery2);
			updateTeamMembersPreparedStatement.setInt(1, projectId);
			updateTeamMembersRowsUpdated = dataAccess.executeUpdate(updateTeamMembersPreparedStatement);
	
			// delete project (delete cascade will delete user stories, tasks, and test cases)
			deleteProjectPreparedStatement = conn.prepareStatement(deleteProjectQuery);
			deleteProjectPreparedStatement.setInt(1, projectId);
			deleteProjectRowsUpdated = dataAccess.executeUpdate(deleteProjectPreparedStatement);
		} catch (SQLException e ) {
			conn.rollback();
			throw e;
		} finally {
	        if (deleteProjectPreparedStatement != null) {
	        	deleteProjectPreparedStatement.close();
	        }
	        if (updateTeamMembersPreparedStatement != null) {
	        	updateTeamMembersPreparedStatement.close();
	        }
	        conn.commit();
	        conn.setAutoCommit(true);
			dataAccess.closeConnection();
		}

		return deleteProjectRowsUpdated + updateTeamMembersRowsUpdated;
	}
}
